﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Configuration;

namespace DataAccess.DatabaseManagement
{
    public class MSSqlServerDAO:IDatabaseClient
    {
        public MSSqlServerDAO()
        {
            getConnection();
        }

        public System.Data.IDbConnection Connection
        {
            get;
            set;
        }

        public System.Data.IDbConnection getConnection()
        {
            string strConnection = "Data Source=localhost;Initial Catalog=servicedesk;User Id=sa;Password=SmallAngel0811;";

            Connection = null;
            try
            {
                Connection = new SqlConnection(strConnection);
            }
            catch (Exception)
            {
                Connection = null;
            }
            return Connection;
        }

        public System.Data.IDataReader ExecuteSelectCommand(string select_cmd)
        {
            SqlDataReader resultset = null;

            this.getConnection();

            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                if (Connection.State == ConnectionState.Open)
                {
                    SqlCommand dbCmd = new SqlCommand(select_cmd, (SqlConnection)Connection);
                    resultset = dbCmd.ExecuteReader();
                }
            }
            catch (Exception)
            {
                resultset = null;
            }
            return resultset;
        }

        public int ExecuteInsertCommand(string insertCmd)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(insertCmd, (SqlConnection)Connection);

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                effectedRow = -1;
            }
           
            return effectedRow;
        }

        public void CloseConnection(bool unsafeclose)
        {
            if (Transaction == null)
            {
                if(!unsafeclose)
                    throw new Exception("Transaction is null, If don't use transaction you must close connection with method CloseConnection(true)");
            }
            if (this.Connection.State == ConnectionState.Open)
            {
                if (Transaction != null) Transaction.Dispose();
                this.Connection.Close();
                this.Connection.Dispose();
            }
        }

        public IDbTransaction Transaction
        {
            get;
            set;
        }

        public void Open()
        {
            if (Connection.State == ConnectionState.Closed)
                Connection.Open();
            Transaction = Connection.BeginTransaction();
        }

        public void Commit()
        {
            Transaction.Commit();
        }

        public void RollBack()
        {
            Transaction.Rollback();
        }

        /// <summary>
        /// value: Select {0} From {1}
        /// example: FormatString(SelectCommand, ColumnNames, Table and Condition) 
        /// </summary>
        public string SelectCommand
        {
            get
            {
                return "Select {0} From {1}";
            }
        }
        /// <summary>
        /// value: Update {0} Set {1} Where {2}
        /// example: FormatString(UpdateCommand, TableName, ColumnName = value, Condition)
        /// </summary>
        public string UpdateCommand
        {
            get
            {
                return "Update {0} Set {1} Where {2}";
            }
        }
        /// <summary>
        /// value: Delete {0} Where {1}
        /// example: FormatString(DeleteCommand, TableName, Condition)
        /// </summary>
        public string DeleteCommand
        {
            get
            {
                return "Delete {0} Where {1}";
            }
        }
        /// <summary>
        /// value: Insert Into {0} Value {1}
        /// example: FormatString(InsertCommand, TableName, ColumnNames, Values) 
        /// </summary>
        public string InsertCommand
        {
            get
            {
                return "Insert {0}{1} Values {2}";
            }
        }

        public int ExecuteInsertCommand(string insertCmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(insertCmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                    iparam.DbType = item.DbType;
                    if (item.DbType == DbType.String)
                        iparam.SqlDbType = SqlDbType.NVarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.SqlDbType = SqlDbType.NVarChar;
                    Cmd.Parameters.Add(iparam);
                }
                if (paramcondition != null)
                {
                    foreach (IParamenter item in paramcondition)
                    {
                        SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                        iparam.DbType = item.DbType;
                        if (item.DbType == DbType.String)
                            iparam.SqlDbType = SqlDbType.NVarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.SqlDbType = SqlDbType.NVarChar;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                effectedRow = -1;
            }
            return effectedRow;
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(select_cmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.SqlDbType = SqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.SqlDbType = SqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (paramcondition != null)
                {
                    foreach (IParamenter item in paramcondition)
                    {
                        SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.SqlDbType = SqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.SqlDbType = SqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                return null;
            }
            return effectedRow;
        }

        public long GetAutoIncrement(string tablename)
        {
            throw new NotImplementedException();
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, IParamenter[] param)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(select_cmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.SqlDbType = SqlDbType.Text;
                    if (item.DbType == DbType.AnsiString)
                        iparam.SqlDbType = SqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                return null;
            }
            return effectedRow;
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, List<IParamenter> param = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(select_cmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (IParamenter item in param)
                    {
                        SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.SqlDbType = SqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.SqlDbType = SqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                return null;
            }
            return effectedRow;
        }

        public int ExecuteInsertCommand(string insertCmd, IParamenter[] param)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(insertCmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.SqlDbType = SqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.SqlDbType = SqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                effectedRow = -1;
            }
            return effectedRow;
        }

        public int ExecuteInsertCommand(string insertCmd, List<IParamenter> param = null)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                SqlCommand Cmd = new SqlCommand(insertCmd, (SqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (IParamenter item in param)
                    {
                        SqlParameter iparam = new SqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.SqlDbType = SqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.SqlDbType = SqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (SqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                effectedRow = -1;
            }
            return effectedRow;
        }

        public List<IParamenter> GetParameter()
        {
            throw new NotImplementedException();
        }


        public DataSet GetDataSetFormSelectCommand(string select_cmd)
        {
            throw new NotImplementedException();
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, IParamenter[] param)
        {
            throw new NotImplementedException();
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            throw new NotImplementedException();
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, List<IParamenter> param = null)
        {
            throw new NotImplementedException();
        }


        public int ExecuteCreateCommand(string select_cmd)
        {
            throw new NotImplementedException();
        }
    }
}